IF OBJECT_ID('ssf.utl_executesql') IS NOT NULL
	DROP PROCEDURE ssf.utl_executesql
GO

CREATE PROCEDURE ssf.utl_executesql
	@sql_statement NVARCHAR(MAX)
	, @database_name NVARCHAR(128) = NULL
	, @row_count BIGINT = NULL OUTPUT
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[
Run the given sql statement, within the given database.
	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE @proc_error INT
	, @sql_error INT
	, @error_message NVARCHAR(2048)
	, @nsql NVARCHAR(MAX)
	, @crlf CHAR(2) = CHAR(13) + CHAR(10)
	, @quote CHAR(1) = CHAR(39)
	, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[check variables]]></code_comment>*/
SET @row_count = NULL
SET @database_name = QUOTENAME(ISNULL(PARSENAME(@database_name, 1), DB_NAME()))

-- exit if no statement
IF NULLIF(LTRIM(RTRIM(@sql_statement)), N'') IS NULL
	RETURN @@ERROR
-- ============

-- ============
/*<code_comment><![CDATA[run sql statement]]></code_comment>*/
BEGIN TRY
	SET @nsql = 'EXECUTE ' + @database_name + '.sys.sp_executesql'
		+ @crlf + @tab + '@sql_statement;'
		+ @crlf
		+ @crlf + 'SET @row_count = ROWCOUNT_BIG();'

	EXECUTE sys.sp_executesql
		@nsql
		, N'@sql_statement NVARCHAR(MAX), @row_count BIGINT OUTPUT'
		, @sql_statement, @row_count OUTPUT
END TRY
BEGIN CATCH
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()
	
	RAISERROR(@error_message, 16, 1)
	RETURN @sql_error
END CATCH
-- ============

RETURN @@ERROR
GO
